package com.dz.risk.common;

import java.io.File;
import java.io.FileOutputStream;
import java.net.URLEncoder;
import java.util.List;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletResponse;

import com.dz.risk.pojo.domain.RiskFactorEntity;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFRichTextString;

/**
 * description: Excel 数据输出
 *
 * @author w
 * @version v1.0
 * @date
 **/
public class ExcelExport {

    /**
     * description: 导出excel --- 支持web
     *
     * @param sheetName sheet表名字
     * @param headers   表头
     * @param dataList  表数据
     * @param fileName  导出文件名
     * @param response
     * @return void
     * @version v1.0
     * @author w
     * @date
     */
    public static void export(String sheetName, String[] headers, String[] secondHeader, List<RiskFactorEntity> dataList, String fileName
            , HttpServletResponse response) throws Exception {
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        createSheet(sheetName, headers, secondHeader, dataList, workbook);
        ServletOutputStream os = response.getOutputStream();
        response.setCharacterEncoding("UTF-8");
        response.setHeader("Content-Disposition", "attachment; filename=" + URLEncoder.encode(fileName, "UTF-8"));
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        workbook.write(os);
        os.flush();

    }

    /**
     * description: 创建sheet表格
     *
     * @param sheetName 表sheet 名字
     * @param headers   表头
     * @param dataList  表数据
     * @param wb
     * @return void
     * @version v1.0
     * @author w
     * @date
     */
    public static void createSheet(String sheetName, String[] headers, String[] secondHeaders, List<RiskFactorEntity> dataList, SXSSFWorkbook wb) {
        SXSSFSheet sheet = wb.createSheet(sheetName);
        // 设置表头和单元格格式
        CellStyle headStyle = setHeaderStyle(wb);
        CellStyle bodyStyle = setBodyStyle(wb);
        // 创建表头和单元格数据
        createHeader(headers, sheet, headStyle);
        createSecondHeader(secondHeaders, sheet, headStyle);
        createBody(dataList, sheet, bodyStyle);
        // 第一行合并
        CellRangeAddress region = new CellRangeAddress(0, 0, 1, 3);
        sheet.addMergedRegion(region);
        region = new CellRangeAddress(0, 0, 4, 6);
        sheet.addMergedRegion(region);
        region = new CellRangeAddress(0, 0, 7, 9);
        sheet.addMergedRegion(region);
        region = new CellRangeAddress(0, 1, 0, 0);
        sheet.addMergedRegion(region);
    }


    /**
     * description: 创建表头
     *
     * @param headers
     * @param sheet
     * @param headStyle
     * @return void
     * @version v1.0
     * @author w
     * @date
     */
    private static void createHeader(String[] headers, SXSSFSheet sheet, CellStyle headStyle) {
        SXSSFRow row = sheet.createRow(0);
        row.setHeightInPoints(16F);
        for (int i = 0; i < 10; i++) {
            // 创建单元格
            SXSSFCell cell = row.createCell(i);
            cell.setCellStyle(headStyle);
            if (i == 0) {
                XSSFRichTextString text = new XSSFRichTextString("压力情景参数（股市跌债市跌）");
                cell.setCellValue(text);
            } else if (i == 1 || i == 2 || i == 3) {
                XSSFRichTextString text = new XSSFRichTextString("未来1个月");
                cell.setCellValue(text);
            } else if (i == 4 || i == 5 || i == 6) {
                XSSFRichTextString text = new XSSFRichTextString("未来2个月");
                cell.setCellValue(text);
            } else if (i == 7 || i == 8 || i == 9) {
                XSSFRichTextString text = new XSSFRichTextString("未来3个月");
                cell.setCellValue(text);
            }
            sheet.trackAllColumnsForAutoSizing();
            sheet.autoSizeColumn(i);
        }
    }

    private static void createSecondHeader(String[] headers, SXSSFSheet sheet, CellStyle headStyle) {
        SXSSFRow row = sheet.createRow(1);
        row.setHeightInPoints(16F);
        for (int i = 0; i < headers.length; i++) {
            // 创建单元格
            SXSSFCell cell = row.createCell(i);
            cell.setCellStyle(headStyle);
            XSSFRichTextString text = new XSSFRichTextString(headers[i]);
            cell.setCellValue(text);
            sheet.trackAllColumnsForAutoSizing();
            sheet.autoSizeColumn(i);
        }
    }

    /**
     * description: 表格中填充数据
     *
     * @param dataList
     * @param sheet
     * @param bodyStyle
     * @return void
     * @version v1.0
     * @author w
     * @date
     */
    private static void createBody(List<RiskFactorEntity> dataList, SXSSFSheet sheet, CellStyle bodyStyle) {
        for (int i = 0; i < dataList.size(); i++) {
            // 从第二行开始，第一行做表头
            SXSSFRow row = sheet.createRow(i + 2);
            for (int j = 0; j < 10; j++) {
                SXSSFCell cell = row.createCell(j);
                cell.setCellStyle(bodyStyle);
                if (j == 0) {
                    XSSFRichTextString text = new XSSFRichTextString(dataList.get(i).getRiskContent());
                    cell.setCellValue(text);
                } else {
                    XSSFRichTextString text = new XSSFRichTextString("");
                    cell.setCellValue(text);
                }
                sheet.trackAllColumnsForAutoSizing();
                sheet.autoSizeColumn(i);
            }
        }
    }

    /**
     * description: 设置单元格内容样式
     *
     * @param wb
     * @return HSSFCellStyle
     * @version v1.0
     * @author w
     * @date
     */
    private static CellStyle setBodyStyle(SXSSFWorkbook wb) {
        // 设置表格单元格格式
        CellStyle style = wb.createCellStyle();
        style.setFillForegroundColor(HSSFColor.HSSFColorPredefined.WHITE.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setBorderTop(BorderStyle.THIN);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setDataFormat(wb.createDataFormat().getFormat("0.00%"));

        // 设置字体格式
        Font font = wb.createFont();
        font.setFontName("微软雅黑");
        style.setFont(font);
        return style;
    }

    /**
     * description: 设置表头样式
     *
     * @param wb
     * @return HSSFCellStyle
     * @version v1.0
     * @author w
     * @date 2020年3月30日 下午2:38:39
     */
    private static CellStyle setHeaderStyle(SXSSFWorkbook wb) {
        // 设置表格单元格格式
        CellStyle style = wb.createCellStyle();
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.GREY_50_PERCENT.getIndex());

        // 设置字体格式
        Font font = wb.createFont();
        font.setFontName("微软雅黑");
        font.setFontHeightInPoints((short) 10);
        style.setFont(font);
        return style;
    }

}
